iT邦幫忙

0

C# DataTable 讀取/匯出Excel (使用EPPlus)

  • 分享至 

  • xImage
  •  

1.使用EPPlus讀取.xlsx檔,需安裝套件 using OfficeOpenXml;

public static DataTable ExcelImport()
{
    string path="你的excel路徑"
    //開啟已存在的Excel檔案
    FileInfo existingFile = new FileInfo(path);
    //new一個包存取.xlsx檔
    ExcelPackage package = new ExcelPackage(existingFile);
    //因為EPPlus 升版和授權,需要加這行
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    //選取第1個sheet
    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
    //取得所選sheet的列數和行數
    int rows = worksheet.Dimension.End.Row;
    int cols = worksheet.Dimension.End.Column;
    //讀取資料放入DataTable
    DataTable dt = new DataTable(worksheet.Name);
    DataRow dr = null;
    //ExcelWorksheet在.net framwork 的index從1開始;在.net從0開始
    for (int i = 1; i <= rows; i++)
    {
        if (i > 1)
            dr = dt.Rows.Add();
        for (int j = 1; j <= cols; j++)
        {
            //把第一列設為DataTable標頭
            if (i == 1)
                dt.Columns.Add(worksheet.Cells[i, j].Value.ToString());
            //剩下的寫入DataTable,不要加ToString(),因為若儲存格空白會導致NullReference
            else
                dr[j - 1] = worksheet.Cells[i, j].Value;
        }
    }
    return dt;
}

2.使用EPPlus匯出Excel(以WinForm DataGridView為例)

//建立datatable
DataTable dt = new DataTable();
//加入 Columns
dt.Columns.Add(new DataColumn(dataGridView.Columns[0].HeaderText, typeof(int)));
dt.Columns.Add(new DataColumn(dataGridView.Columns[1].HeaderText, typeof(string)));
dt.Columns.Add(new DataColumn(dataGridView.Columns[2].HeaderText, typeof(string)));
dt.Columns.Add(new DataColumn(dataGridView.Columns[3].HeaderText, typeof(string)));
//加入 Rows,Count-1是dataGridView有預設空白列
for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
{
    DataRow dr = dt.NewRow();
    for (int j = 0; j < dataGridView.Rows[i].Cells.Count; j++)
        dr[j] = dataGridView.Rows[i].Cells[j].Value;
    dt.Rows.Add(dr);
}
using (ExcelPackage package = new ExcelPackage())
{
    string path;
    SaveFileDialog save = new SaveFileDialog();
    save.FileName = $"{comboboxAward.Text}_{labelAwardQuota.Text}_{DateTime.Now:yyyyMMdd}.xlsx";
    save.Filter = "(*.xlsx)|*.xlsx";
    save.Title = "另存新檔";
    if (save.ShowDialog() == DialogResult.OK)
    {
        //取得使用者儲存Excel的路徑
        path = Path.GetFullPath(save.FileName);
        // 新增worksheet
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("工作表2");
        //新增DataTable到sheet
        worksheet.Cells["A1"].LoadFromDataTable(dt, true);
        package.SaveAs(path);
    }
}

重點:
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("工作表2");
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
package.SaveAs(path);


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言